package com.tsd.core.datasource;

import cn.hutool.core.map.MapUtil;
import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSON;
import com.tsd.core.constants.Constants;
import com.tsd.core.constants.VersionConstants;
import com.tsd.core.utils.ExceptionUtil;
import com.tsd.core.utils.HlpUtils;
import com.tsd.core.utils.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.util.*;

/**
 * @ClassName: DataSourceUtils
 * @Description: TODO
 * @Author: Hillpool
 * @Date: 2024/4/16 9:57
 * @Version: 1.0
 */
public class DataSourceUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceUtils.class);

    /**
     * 创建数据源
     *
     * @param entity
     * @param isLog
     * @return
     */
    public static DataSource newDataSource(Entity entity, boolean isLog) {
        String url = createDBUrl(entity, isLog);
        String driver = entity.getStr("driver");
        String dbUserName = entity.getStr("db_user_name");
        String dbPassword = entity.getStr("db_password");
        return newDataSource(driver, url, dbUserName, dbPassword);
    }

    /**
     * 创建数据源
     *
     * @param driverClassName
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static DataSource newDataSource(String driverClassName, String url, String username, String password) {
        try {
            DruidDataSource dataSource = new DruidDataSource();
            //基本属性 url username password driverClassName
            dataSource.setDriverClassName(driverClassName);
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            //配置初始化大小、最小、最多连接数
            dataSource.setInitialSize(1);
            dataSource.setMaxActive(500);
            dataSource.setMinIdle(5);
            //配置获取连接等待超时时间
            dataSource.setMaxWait(10000);
            //配置间隔多久进行一次检测，检测需要关闭的空闲连接，单位是毫秒
            dataSource.setTimeBetweenEvictionRunsMillis(60000);
            //配置一个连接在连接池中，最小生存的时间，单位是毫秒
            dataSource.setMinEvictableIdleTimeMillis(60000);
            //链接使用超过时间限制是否回收
            dataSource.setRemoveAbandoned(false);
            //超过时间限制时间（单位秒），目前为5分钟，如果有业务处理时间超过5分钟，可以适当调整。
            dataSource.setRemoveAbandonedTimeout(180);
            //链接回收的时候控制台打印信息，测试环境可以加上true，线上环境false。会影响性能。
            dataSource.setLogDifferentThread(true);
            //验证连接有效与否的SQL，不同的数据配置不同
            dataSource.setValidationQuery("SELECT 1 ");
            //此项配置为true即可，不影响性能，并且保证安全性。意义为：申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
            dataSource.setTestWhileIdle(true);
            //这里建议配置为TRUE，防止取到的连接不可用。获取链接的时候，不校验是否可用，开启会有损性能
            dataSource.setTestOnBorrow(true);
            //归还链接到连接池的时候校验链接是否可用
            dataSource.setTestOnReturn(false);
            //打开PSCache，并且指定每个连接上的PSCache的大小
            dataSource.setPoolPreparedStatements(false);
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
            //连接出错后再尝试连接次数
            dataSource.setBreakAfterAcquireFailure(true);
            dataSource.setConnectionErrorRetryAttempts(10);
            return dataSource;
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        }
        return null;
    }

    /**
     * 构建数据库链接
     *
     * @param entity
     * @param isLog  是否日志库
     * @return
     */
    public static String createDBUrl(Entity entity, boolean isLog) {
        String url = entity.getStr("db_url");
        if (isLog) {
            String dbName = entity.getStr("db_name");
            url = url.replace("/" + dbName + "?", "/" + dbName + Constants.DATABASE_SUFFIX_FOR_LOG + "?");
        }
        return url;
    }

    /**
     * 检查表是否存在
     *
     * @param dataSource
     * @param tableName
     * @return
     */
    public static boolean isTableExist(DataSource dataSource, String tableName) {
        Db db = DbUtil.use(dataSource);
        return isTableExist(db, tableName);
    }

    /**
     * 检查表是否存在
     *
     * @param db
     * @param tableName
     * @return
     */
    public static boolean isTableExist(Db db, String tableName) {
        Map<String, String> params = MapUtil.builder("table", tableName).build();
        try {
            Number count = db.queryNumber("SELECT IFNULL(COUNT(1), 0) FROM `information_schema`.`TABLES` " +
                    "WHERE `table_schema`=(SELECT DATABASE()) AND `table_name`=@table", params);
            return count != null && count.intValue() > 0;
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
        }
        return false;
    }

    /**
     * 生成日志账套信息
     *
     * @param entity
     * @return
     */
    public static Entity copy4Log(Entity entity) {
        Entity logEntity = new Entity();
        logEntity.put("sid", entity.getStr("sid") + Constants.DATABASE_SUFFIX_FOR_LOG);
        logEntity.put("code", entity.getStr("code") + Constants.DATABASE_SUFFIX_FOR_LOG);
        logEntity.put("db_host", entity.getStr("db_host"));
        logEntity.put("db_port", entity.getStr("db_port"));
        logEntity.put("db_name", entity.getStr("db_name") + Constants.DATABASE_SUFFIX_FOR_LOG);
        logEntity.put("driver", entity.getStr("driver"));
        String dbUrl = entity.getStr("db_url");
        String replaceStr = "/" + entity.getStr("db_name") + "?";
        String targetStr = "/" + logEntity.getStr("db_name") + "?";
        dbUrl = dbUrl.replace(replaceStr, targetStr);
        logEntity.put("db_url", dbUrl);
        logEntity.put("db_user_name", entity.getStr("db_user_name"));
        logEntity.put("db_password", entity.getStr("db_password"));
        logEntity.put("mode", JSON.toJSONString(Collections.singletonList("log")));
        logEntity.put("log", true);
        return logEntity;
    }

    /**
     * 获取主账套信息
     *
     * @param masterDataSource
     * @param properties
     * @return
     * @throws Exception
     */
    public static Entity gen4Master(DruidDataSource masterDataSource, DataSourceProperties properties) throws Exception {
        DruidPooledConnection connection = masterDataSource.getConnection();
        Entity entity = new Entity();
        entity.put("id", 0L);
        entity.put("sid", DataSourceContextHolder.DEFAULT_ACCOUNT_SET_SID);
        entity.put("code", DataSourceContextHolder.DEFAULT_ACCOUNT_SET_SID);
        Map<String, String> dbInfo = JdbcUtil.parseJdbcUrl(properties.getUrl());
        entity.put("db_host", dbInfo.get("host"));
        entity.put("db_port", Integer.parseInt(dbInfo.get("port")));
        entity.put("db_name", connection.getCatalog());
        entity.put("driver", properties.getDriverClassName());
        entity.put("db_url", properties.getUrl());
        entity.put("db_user_name", properties.getUsername());
        entity.put("db_password", properties.getPassword());
        entity.put("mode", JSON.toJSONString(Arrays.asList("tsd", "master")));
        entity.put("state", Constants.STATE_DB_CREATED);
        entity.put("log", false);
        entity.put("version", VersionConstants.MODULE_VERSION_ULTIMATE);
        return entity;
    }

    /**
     * 创建日志表
     *
     * @param dataSource
     */
    public static void createTableLogInfo4AccountSet(DataSource dataSource) {
        try {
            boolean tableExist = isTableExist(dataSource, Constants.TABLE_NAME_SYS_LOG_INFO);
            if (!tableExist) {
                return;
            }
            Db db = DbUtil.use(dataSource);
            //当月日志表
            Date now = new Date();
            String suff = HlpUtils.date2String(now, "yyyyMM");
            String sql = "CREATE TABLE IF NOT EXISTS sys_log_info_" + suff + "   LIKE sys_log_info";
            db.execute(sql);

            //下月日志表
            Date nextMonth = HlpUtils.getNextDateByMonth(now, 1);
            suff = HlpUtils.date2String(nextMonth, "yyyyMM");
            sql = "CREATE TABLE IF NOT EXISTS sys_log_info_" + suff + "   LIKE sys_log_info";
            db.execute(sql);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
        }
    }

    /**
     * 根据sid查询账套信息
     *
     * @param dataSource
     * @param sid
     * @return
     */
    public static Entity getAccountSetBySid(DataSource dataSource, String sid) {
        Db db = DbUtil.use(dataSource);
        return getAccountSetBySid(db, sid);
    }

    /**
     * 根据sid查询账套信息
     *
     * @param db
     * @param sid
     * @return
     */
    public static Entity getAccountSetBySid(Db db, String sid) {
        try {
            boolean tableExist = isTableExist(db, Constants.TABLE_NAME_SYS_ACCOUNT_SET);
            if (!tableExist) {
                return null;
            }
            Map<String, String> params = MapUtil.builder("sid", sid).build();
            return db.queryOne("SELECT * FROM sys_account_set WHERE dr=0 AND sid=@sid", params);
        } catch (Exception e) {
            LOGGER.error(ExceptionUtil.getStackTraceFromException(e));
        }
        return null;
    }

    /**
     * 查询有效账套信息
     *
     * @param dataSource
     * @return
     */
    public static List<Entity> getValidAccountSetList(DataSource dataSource) {
        Db db = DbUtil.use(dataSource);
        return getValidAccountSetList(db);
    }

    /**
     * 查询有效账套信息
     *
     * @param db
     * @return
     */
    public static List<Entity> getValidAccountSetList(Db db) {
        try {
            boolean tableExist = isTableExist(db, Constants.TABLE_NAME_SYS_ACCOUNT_SET);
            if (!tableExist) {
                return null;
            }
            return db.query("SELECT * FROM sys_account_set WHERE dr=0 AND state=2");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
